package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.close;
import static cam.School.DataAccessObject.DAOUtil.prepareStatement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.WeeklyPlanDAO;
import cam.School.DataTransfertObject.CallResult;
import cam.School.DataTransfertObject.StudentDTO;
import cam.School.DataTransfertObject.UserDTO;
import cam.School.DataTransfertObject.WeeklyPlanDTO;


public class WeeklyPlanDAOImpl implements WeeklyPlanDAO  {
	private static final String QUERY_PARAMS_GET_ALL ="id, subjectId, start, finish,"+
													  "day, employeeId, classId";

private static final String UPDATE_QUERY_PARAMS = " id = ?, subjectId = ?, start = ?, finish = ?,"+
													  "day = ?, employeeId = ?, classId = ?";

private static final String SQL_FIND_ALL = "SELECT "+ 
QUERY_PARAMS_GET_ALL + " FROM weeklyplan";

private static final String SQL_FIND_BY_ID = "SELECT "+ 
QUERY_PARAMS_GET_ALL + " FROM weeklyplan where id = ?";

private static final String SQL_FIND_BY_SUBJECT_ID = "SELECT "+ 
QUERY_PARAMS_GET_ALL + " FROM weeklyplan WHERE  subjectId = ?";

private static final String SQL_FIND_BY_EMPLOYEE_ID = "SELECT "+ 
QUERY_PARAMS_GET_ALL + " FROM weeklyplan WHERE  employeeId = ?";



private static final String SQL_FIND_BY_WEEKLYPLAN_ID = "SELECT "+ 
QUERY_PARAMS_GET_ALL + " FROM weeklyplan WHERE  id = ?";

private static final String SQL_FIND_BY_CLASS_ID = "SELECT "+ 
QUERY_PARAMS_GET_ALL + " FROM weeklyplan WHERE  classId = ?";

private static final String INSERT_PARAMS = "(subjectId, start, finish, day, employeeId, classId)";

private static final String SQL_INSERT_WEEKLY_PLAN =
"INSERT INTO weeklyplan "+INSERT_PARAMS+" VALUES (?, ?, ?, ?, ?, ?)";

private static final String SQL_UPDATE_EMPLOYEE_WEEKLY_PLAN =
"UPDATE weeklyplan SET " + UPDATE_QUERY_PARAMS +" where employeeId = ?";

private static final String SQL_UPDATE_CLASS_WEEKLY_PLAN =
"UPDATE weeklyplan SET " + UPDATE_QUERY_PARAMS +" where employeeId = ?";

private static final String SQL_DELETE_EMPLOYEE_WEEKLY_PLAN =
"DELETE from weeklyplan where employeeId = ?";

private static final String SQL_DELETE_CLASS_WEEKLY_PLAN =
"DELETE from weeklyplan where employeeId = ?";


private DAOFactory daoFactory;

// Constructors -------------------------------------------------------------------------------

/**
* Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
* inside the DAO package only.
* @param daoFactory The DAOFactory to construct this User DAO for.
*/
public WeeklyPlanDAOImpl(DAOFactory daoFactory) {
	this.daoFactory = daoFactory;
}
	@Override
	public ArrayList<WeeklyPlanDTO> readResultSet(ResultSet resultSet,
			ArrayList<String> columnNameCollection) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
	
	public CallResult executeSQL(Object[] values, String sql, String message) {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {
			connection = daoFactory.getConnection();
						
			preparedStatement = prepareStatement(connection, sql, false,
				values);
			int affectedRows = preparedStatement.executeUpdate();			
			if (affectedRows == 0) {				
				return new CallResult(404, message);			
			} 
			return  new CallResult(200, "Success");	
			
		} catch (SQLException e) {
			return new CallResult(406, e.toString());
		} finally {
			close(connection, preparedStatement, generatedKeys);
		}
	}
	
	public CallResult insert(WeeklyPlanDTO weeklyPlan) {
		// TODO Auto-generated method stub
		Object[] values = {
				weeklyPlan.getSubjectId(),
				weeklyPlan.getStart(),
				weeklyPlan.getFinish(),
				weeklyPlan.getDay(),
				weeklyPlan.getEmployeeId(),
				weeklyPlan.getClassId()
		};		
		return executeSQL(values, SQL_INSERT_WEEKLY_PLAN, "not created");
	}
	
	public CallResult update(WeeklyPlanDTO weeklyPlan, String sql) {
		// TODO Auto-generated method stub
		Object[] values = {
				weeklyPlan.getSubjectId(),
				weeklyPlan.getStart(),
				weeklyPlan.getFinish(),
				weeklyPlan.getDay(),
				weeklyPlan.getEmployeeId(),
				weeklyPlan.getClassId()
		};
		
		return executeSQL(values, sql, "not created");
				
	}

	
	public CallResult delete(long weeklyPlanId, String sql) {
		// TODO Auto-generated method stub
		
		Object[] values = {
					weeklyPlanId
			};
		
		return executeSQL(values, sql, "not created");
		
	}

	private WeeklyPlanDTO map(ResultSet resultSet)
			throws SQLException {
		WeeklyPlanDTO weeklyPlan = new WeeklyPlanDTO();
		weeklyPlan.setId(resultSet.getLong("id"));			
		weeklyPlan.setSubjectId(resultSet.getLong("subjectid"));
		weeklyPlan.setStart(resultSet.getString("start"));
		weeklyPlan.setFinish(resultSet.getString("finish"));		
		weeklyPlan.setEmployeeId(resultSet.getLong("employeeId"));		
		weeklyPlan.setClassId(resultSet.getLong("classId"));		
		return weeklyPlan;
	}
	
	public List<WeeklyPlanDTO> getWeeklyPlans(String sql, long idParameter) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<WeeklyPlanDTO> weeklyPlans = new ArrayList<WeeklyPlanDTO>();
		
		try {
			connection = daoFactory.getConnection();
						
			if(idParameter != -1){
				Object[] values = {
						idParameter
				};
				preparedStatement = prepareStatement(connection, sql, false, values);
			}else{
				preparedStatement = connection.prepareStatement(sql);
			}		
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				weeklyPlans.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return weeklyPlans;
	}
	
	@Override
	public List<WeeklyPlanDTO> findAll() {
		// TODO Auto-generated method stub
		return getWeeklyPlans(SQL_FIND_ALL, -1);
	}

	@Override
	public WeeklyPlanDTO findById(long weeklyPlanId) {
		// TODO Auto-generated method stub
		return getWeeklyPlans(SQL_FIND_BY_ID, weeklyPlanId).get(0);
	}

	@Override
	public List<WeeklyPlanDTO> findByEmployeeId(long weeklyPlanId) {
		// TODO Auto-generated method stub
		return getWeeklyPlans(SQL_FIND_BY_EMPLOYEE_ID, weeklyPlanId);
	}

	@Override
	public List<WeeklyPlanDTO> findByClassId(long weeklyPlanId) {
		// TODO Auto-generated method stub
		return getWeeklyPlans(SQL_FIND_BY_CLASS_ID, weeklyPlanId);
	}

	@Override
	public List<WeeklyPlanDTO> findBySubjectId(long weeklyPlanId) {
		// TODO Auto-generated method stub
		return getWeeklyPlans(SQL_FIND_BY_SUBJECT_ID, weeklyPlanId);
	}

	@Override
	public List<WeeklyPlanDTO> findByDate(long weeklyPlanId) {
		// TODO Auto-generated method stub
		return getWeeklyPlans(SQL_FIND_BY_WEEKLYPLAN_ID, weeklyPlanId);
	}
	@Override
	public CallResult insertEmployeeWeeklyPlan(WeeklyPlanDTO weeklyPlan) {
		// TODO Auto-generated method stub
		return insert(weeklyPlan);
	}
	@Override
	public CallResult insertClassWeeklyPlan(WeeklyPlanDTO weeklyPlan) {
		// TODO Auto-generated method stub
		return insert(weeklyPlan);
	}
	@Override
	public CallResult updateEmployeeWeeklyPlan(WeeklyPlanDTO weeklyPlan) {
		// TODO Auto-generated method stub
		return update(weeklyPlan, SQL_UPDATE_EMPLOYEE_WEEKLY_PLAN);
	}
	@Override
	public CallResult updateClassWeeklyPlan(WeeklyPlanDTO weeklyPlan) {
		// TODO Auto-generated method stub
		return update(weeklyPlan, SQL_UPDATE_CLASS_WEEKLY_PLAN);
	}
	@Override
	public CallResult deleteEmployeeWeeklyPlan(long employeeId) {
		// TODO Auto-generated method stub
		return delete(employeeId, SQL_DELETE_EMPLOYEE_WEEKLY_PLAN);
	}
	@Override
	public CallResult deleteClassWeeklyPlan(long classId) {
		// TODO Auto-generated method stub
		return delete(classId, SQL_DELETE_CLASS_WEEKLY_PLAN);
	}

}
